package au.edu.utas.yu_qiao_group.luckyman.db.tickets;


import android.content.ContentValues;
import android.database.Cursor;
import android.database.sqlite.SQLiteDatabase;

import java.util.ArrayList;

import au.edu.utas.yu_qiao_group.luckyman.db.raffles.RaffleDTO;
import au.edu.utas.yu_qiao_group.luckyman.db.tickets.TicketDTO;


import android.content.ContentValues;
import android.database.Cursor;
import android.database.sqlite.SQLiteDatabase;

import java.util.ArrayList;
import java.util.HashSet;
import java.util.Iterator;
import java.util.Set;

public class TicketTable {
  public static final String TABLE_NAME = "tickets";
  public static final String TRIGGER_NAME_1 = "tickets_update_timestamp_trigger";

  public static final String KEY_TICKET_ID = "id";
  public static final String KEY_RAFFLE_ID = "raffle_id";
  public static final String KEY_TICKET_NO = "ticket_no";
  public static final String KEY_ORDER_NO = "order_no";
  public static final String KEY_CUSTOMER_PHONE = "customer_phone";
  public static final String KEY_PURCHASED_PRICE = "ticket_total";
  public static final String KEY_JACKPOT_FLAG = "jackpot_flg";
  public static final String KEY_CREATE_TIME = "create_time";
  public static final String KEY_UPDATE_TIME = "update_time";
  public static final String KEY_DEL_FLAG = "del_flg";

  public static final int TICKET_NO_LENGTH = 4;

  public static final String CREATE_STATEMENT = "CREATE TABLE IF NOT EXISTS " + TABLE_NAME
          + " (" + KEY_TICKET_ID + " integer primary key autoincrement, "
          + KEY_RAFFLE_ID + " integer not null, "
          + KEY_TICKET_NO + " char(" + TICKET_NO_LENGTH + ") not null, "
          + KEY_ORDER_NO + " char(20) not null, "
          + KEY_CUSTOMER_PHONE + " varchar not null, "
          + KEY_PURCHASED_PRICE + " integer not null, "
          + KEY_JACKPOT_FLAG + " tinyint default 0, "
          + KEY_CREATE_TIME + " datetime default current_timestamp, "
          + KEY_UPDATE_TIME + " datetime default current_timestamp, "
          + KEY_DEL_FLAG + " tinyint default 0"
          + ");";

  public static final String CREATE_TRIGGER_STATEMENT = "CREATE TRIGGER " + TRIGGER_NAME_1
          + " AFTER UPDATE ON " + TABLE_NAME
          + " BEGIN"
          + " UPDATE " + TABLE_NAME + " SET " + KEY_UPDATE_TIME + " = current_timestamp WHERE "
          + KEY_TICKET_ID + " = NEW." + KEY_TICKET_ID
          + "; END;";

  public static TicketDTO createFromCursor(Cursor c) {
    if (c == null || c.isAfterLast() || c.isBeforeFirst()) {
      return null;
    } else {
      TicketDTO t = new TicketDTO(
              c.getInt(c.getColumnIndex(KEY_TICKET_ID)),
              c.getInt(c.getColumnIndex(KEY_RAFFLE_ID)),
              c.getString(c.getColumnIndex(KEY_TICKET_NO)),
              c.getString(c.getColumnIndex(KEY_ORDER_NO)),
              c.getString(c.getColumnIndex(KEY_CUSTOMER_PHONE)),
              c.getInt(c.getColumnIndex(KEY_PURCHASED_PRICE)),
              c.getInt(c.getColumnIndex(KEY_JACKPOT_FLAG)),
              c.getLong(c.getColumnIndex(KEY_CREATE_TIME))
      );
      return t;
    }
  }

  public static void insert(SQLiteDatabase db, TicketDTO t) {

    ContentValues values = new ContentValues();
    values.put(KEY_RAFFLE_ID, t.getMRaffleID());
    values.put(KEY_TICKET_NO, t.getMTicketNo());
    values.put(KEY_ORDER_NO, t.getMOrderNo());
    values.put(KEY_CUSTOMER_PHONE, t.getMCustomerPhone());
    values.put(KEY_PURCHASED_PRICE, t.getMPurchasePrice());
    values.put(KEY_JACKPOT_FLAG, t.getMJackpotFlag());
    values.put(KEY_CREATE_TIME, t.getMCreateTime());
    db.insert(TABLE_NAME, null, values);
  }

  public static ArrayList<TicketDTO> selectAll(SQLiteDatabase db) {
    ArrayList<TicketDTO> results = new ArrayList<TicketDTO>();
    Cursor c = db.query(TABLE_NAME, null, KEY_DEL_FLAG + " = ?", new String[]{"0"}, null, null, KEY_CREATE_TIME);
    if (c != null) {
      c.moveToFirst();
      while (!c.isAfterLast()) {
        results.add(createFromCursor(c));
        c.moveToNext();
      }
    }
    return results;
  }

  public static TicketDTO getByID(SQLiteDatabase db, int id) {
    Cursor c = db.query(TABLE_NAME, null, KEY_TICKET_ID + " = ? and " + KEY_DEL_FLAG + " = ?", new String[]{"" + id, "0"}, null, null, null);
    c.moveToFirst();
    return createFromCursor(c);
  }

  public static TicketDTO getByNO(SQLiteDatabase db, long raffleId, String ticketNo) {
    Cursor c = db.query(TABLE_NAME, null, KEY_RAFFLE_ID + " = ? and " + KEY_TICKET_NO + " = ? and " + KEY_DEL_FLAG + " = ?", new String[]{"" + raffleId, ticketNo, "0"}, null, null, null);
    c.moveToFirst();
    return createFromCursor(c);
  }

  public static ArrayList<TicketDTO> getCustomerDistinct (SQLiteDatabase db) {
    ArrayList<TicketDTO> results = new ArrayList<TicketDTO>();
    Cursor c = db.query(true, TABLE_NAME, null, null, null, KEY_CUSTOMER_PHONE, null, null, null);
    if (c != null) {
      c.moveToFirst();
      while (!c.isAfterLast()) {
        results.add(createFromCursor(c));
        c.moveToNext();
      }
    }
    return results;
  }

  public static ArrayList<TicketDTO> getAllByRaffleID(SQLiteDatabase db, long id) {
    ArrayList<TicketDTO> results = new ArrayList<TicketDTO>();
    Cursor c = db.query(TABLE_NAME, null, KEY_RAFFLE_ID + " = ? and " + KEY_DEL_FLAG + " = ?",
            new String[]{"" + id, "0"}, null, null, KEY_CREATE_TIME);
    if (c != null) {
      c.moveToFirst();
      while (!c.isAfterLast()) {
        results.add(createFromCursor(c));
        c.moveToNext();
      }
    }
    return results;
  }

  public static ArrayList<TicketDTO> getAllByCustomerPhone(SQLiteDatabase db, RaffleDTO r, String p) {
    ArrayList<TicketDTO> results = new ArrayList<TicketDTO>();
    Cursor c = db.query(TABLE_NAME, null, KEY_RAFFLE_ID + " = ? and " + KEY_CUSTOMER_PHONE + " = ? and " + KEY_DEL_FLAG + " = ?",
            new String[]{"" + r.getMRaffleID(), p, "0"}, null, null, null);
    if (c != null) {
      c.moveToFirst();
      while (!c.isAfterLast()) {
        results.add(createFromCursor(c));
        c.moveToNext();
      }
    }
    return results;
  }

  public static void update(SQLiteDatabase db, TicketDTO t) {
    ContentValues values = new ContentValues();
    values.put(KEY_CUSTOMER_PHONE, t.getMCustomerPhone());
    values.put(KEY_PURCHASED_PRICE, t.getMPurchasePrice());
    values.put(KEY_JACKPOT_FLAG, t.getMJackpotFlag());
    db.update(TABLE_NAME, values, KEY_TICKET_ID + "= ? and " + KEY_DEL_FLAG + " = ?", new String[]{"" + t.getMTicketID(), "0"});
  }

  public static void delete(SQLiteDatabase db, TicketDTO t) {
    ContentValues values = new ContentValues();
    values.put(KEY_DEL_FLAG, 1);
    db.update(TABLE_NAME, values, KEY_TICKET_ID + "= ?", new String[]{"" + t.getMTicketID()});
  }
}
